Create a calculated field to determine sale amount

The report must show the total sale amount, which is not a field in the database, so it must be calculated.  The total sale amount is equal to the product quantity multiplied by the unit cost, which are fields in the database.  A calculated field must be created and added as an entry underneath the Visible Fields tab.

Within the Visible Fields tab:

  1. Click the empty Table field to the right of the "Products" table entry.
  2. Click the down arrow that appears in the field to get a list of available tables.
  3. Select '<calculated>' from the drop-down.
    This shows the drop down that appears when you click on the down arrow that appears after clicking in the table field.
  4. Click inside the Field field (below the Table field), then click the ellipses icon ellipses icon that appears inside the field to launch the SQL Editor. Enter the following into the text field:
    = Order_Details.quantity * Products.unit_price
    This shows the SQL Editor and contains the statement which multiplies quantity times unit prices.
  5. Click OK to return to the Build Query.
  6. Click in the Type field, then click the down arrow within the field to get a drop-down with a list of data types.
  7. Select Float from the drop-down.
    This shows the drop down for the type field. The type selected is float.
  8. Click in the As field and enter 'sale_amount'. This new calculated field can now be used in the same fashion as other fields in the database.
  9. Click OK, then Finish to continue. 
    Sale amount filled into the as field for the calculated value.

You can now see the new multi-column list box object shown within the DataBlock Designer with all of the fields added.

The dashboard in the designer window with the new multicolumn listbox. The new listbox has column headers matching the fields selected in the query.